package com.braveo.dict;

import java.util.logging.Level;
import java.util.logging.Logger;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class BraveRememberDbAdapter {
	private static final String DB_NAME = "Memory.db";
	private static final String TABLE_NAME = "memory";
	private static final int DB_VERSION = 1;
	
	private SQLiteDatabase db;

	private static BraveRememberDbAdapter instance = null;
	
	private static final String[] DB_CREATE = {
		"CREATE TABLE memory ( _id INTEGER PRIMARY KEY AUTOINCREMENT, " +
			"Word TEXT NOT NULL, Time LONG NOT NULL );",
		"CREATE INDEX word_index ON memory (Word);",
		"CREATE INDEX time_index ON memory (Time);",
	};

	@SuppressWarnings("unused")
	private static final String [] EXTRA_DB_CREATE = {
	"CREATE TABLE android_metadata (locale TEXT);",
	"INSERT INTO android_metadata(locale) VALUES('en_US');"
	};
	
	/**
	 * Do nothing in the contructor
	 */
	private BraveRememberDbAdapter(Context ctx){
		//this.ctx = ctx;
	}
	
	public static synchronized BraveRememberDbAdapter getInstance(Context ctx){
		if(instance==null){
			instance = new BraveRememberDbAdapter(ctx);
		}
		instance.open();
		return instance;
	}
	
	public void update_database(){
		boolean table_exists = false;
		db.execSQL("DROP TABLE IF EXISTS memory_old;");
		
		try{
			db.rawQuery("SELECT count(*) FROM memory;", null);
			table_exists = true;
			db.execSQL("ALTER TABLE memory RENAME TO memory_old;");
		}catch(SQLException e){
			//do nothing
		}

		db.execSQL("DROP INDEX IF EXISTS word_index;");
		db.execSQL("DROP INDEX IF EXISTS time_index;");
		create_database();
		
		if(table_exists){
			db.execSQL("INSERT INTO memory(_id, Word, Time) SELECT _id, Word, Time FROM memory_old;");
			db.execSQL("DROP TABLE memory_old;");
		}
	}
	
	public void create_database(){
		for(int k=0; k<DB_CREATE.length; k++){
			db.execSQL(DB_CREATE[k]);
		}
		db.setVersion(DB_VERSION);
	}
	
	private synchronized boolean open() throws SQLException {
		if(db==null){
			String db_path = BraveDictDbAdapter.DB_DIRECTORY + "/" + DB_NAME;			
			Logger.getLogger(this.getClass().getName()).log(Level.INFO, db_path);
			db = SQLiteDatabase.openOrCreateDatabase(db_path, null);
			if(db.needUpgrade(DB_VERSION)){
				update_database();
			}
		}
		return true;
	}
	
	public synchronized void close() throws SQLException {
		if(db!=null)
			db.close();
		db = null;
	}

	public Cursor getAllWords(){
		return db.query(TABLE_NAME, new String[] { "_id", "Word"}, null, null, null, null, "Time DESC");
	}
	
	public boolean addHistory(String word){
		if(word==null)
			return false;
		word = word.trim();
		if(word.length()==0)
			return false;
		
		String sql = "INSERT INTO " + TABLE_NAME + " (Word, Time) VALUES(?, ?);";
		long t = java.util.Calendar.getInstance().getTimeInMillis() / 1000;
		db.execSQL(sql, new Object[] {word, t});
		return true;
	}
}
